﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace OpenMobileStoreModel
{
    public class Product
    {
        private long mId;
        private long mRevision;
        private long mType;
        private string mCode;
        private string mName;
        private double mQuantity;
        private double mPrice;

        public static SQLiteConnection Db
        {
            get
            {
                return Store.Instance.Db;
            }
        }

        public long Id
        {
            get
            {
                return mId;
            }
        }

        public long Revision
        {
            get
            {
                return mRevision;
            }
        }

        public string Code
        {
            get
            {
                return mCode;
            }
        }

        public string Name
        {
            get
            {
                return mName;
            }
        }

        public double Price
        {
            get
            {
                return mPrice;
            }
        }

        public Double Quantity
        {
            get
            {
                return mQuantity;
            }
        }

        public long Type
        {
            get
            {
                return mType;
            }
        }

        /// <summary>
        /// Search for product by ID.
        /// Throw ResourceNotFoundException if not found.
        /// </summary>
        /// <param name="aId"></param>
        /// <returns></returns>
        public static Product GetById(long aId)
        {
            Db.Open();
            using (Db)
            {
                SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM product WHERE id=@id LIMIT 1;", Db);
                using (cmd)
                {
                    SQLiteParameter prm = new SQLiteParameter("@id", DbType.Int64);
                    prm.Value = aId;
                    cmd.Parameters.Add(prm);
                    SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                    if (rdr.Read())
                    {
                        return new Product(rdr);
                    }
                    else
                    {
                        throw new ResourceNotFoundException("Product not found");
                    }
                }
            }
        }

        /// <summary>
        /// Search for product by code.
        /// Throw ResourceNotFoundException if not found.
        /// </summary>
        /// <param name="aCode"></param>
        /// <returns></returns>
        public static Product GetByCode(string aCode)
        {
            Db.Open();
            using (Db)
            {
                SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM product WHERE code=@code LIMIT 1;", Db);
                using (cmd)
                {
                    SQLiteParameter prm = new SQLiteParameter("@code", DbType.String);
                    prm.Value = aCode;
                    cmd.Parameters.Add(prm);
                    SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                    if (rdr.Read())
                    {
                        return new Product(rdr);
                    }
                    else
                    {
                        throw new ResourceNotFoundException("Product not found");
                    }
                }
            }
        }
        
        /// <summary>
        /// Creates new product instance and stores it into database.
        /// </summary>
        /// <param name="aCode"></param>
        /// <param name="aName"></param>
        /// <returns></returns>
        public static Product New(string aCode, string aName)
        {
            Db.Open();
            using (Db)
            {
                SQLiteCommand cmd = new SQLiteCommand("INSERT INTO product (code, name) VALUES (@code, @name);", Db);
                using (cmd)
                {
                    SQLiteParameter prmCode = new SQLiteParameter("@code", DbType.String);
                    prmCode.Value = aCode;
                    cmd.Parameters.Add(prmCode);
                    SQLiteParameter prmName = new SQLiteParameter("@name", DbType.String);
                    prmName.Value = aName;
                    cmd.Parameters.Add(prmName);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT * FROM product WHERE id=last_insert_rowid()";
                    SQLiteDataReader rdr = cmd.ExecuteReader();
                    rdr.Read();
                    return new Product(rdr);
                }
            }     
        }

        private Product(SQLiteDataReader aReader)
        {
            mId = aReader.GetInt64(0);
            mRevision = aReader.GetInt64(1);
            mType = aReader.GetInt64(2);
            mCode = aReader.GetString(3);
            mName = aReader.GetString(4);
            mQuantity = aReader.GetDouble(5);
            mPrice = aReader.GetDouble(6);
        }
    }
}
